﻿Imports System.Collections.Generic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Linq


Public Class SqlServerMetadataReader
    Implements IMetadataReader

#Region "Constants"

    'One-to-many, e.g. get child objects
    Private Const SQL_GETREFTBL_PAR_TBL As String = "@ReferencedTable"
    Private Const SQL_GETREFTBL As String =
        "SELECT " &
            "f.name AS ForeignKeyName, " &
            "OBJECT_NAME(f.parent_object_id) AS ForeignKeyTableName, " &
            "COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ForeignKeyColumnName, " &
            "OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName, " &
            "COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName " &
        "FROM sys.foreign_keys AS f " &
            "INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id " &
        "WHERE OBJECT_NAME (f.referenced_object_id) = " & SQL_GETREFTBL_PAR_TBL

#End Region


    'http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72957

    Private ReadOnly connectionStr As String

    Public Sub New(connectionStr As String)
        Me.connectionStr = connectionStr
    End Sub

    Public Function GetTableDetails(table As Table, owner As String) As List(Of Column) Implements IMetadataReader.GetTableDetails
        Dim columns = New List(Of Column)()
        Dim conn = New SqlConnection(connectionStr)
        conn.Open()
        Try
            Using conn
                Using tableDetailsCommand = conn.CreateCommand()
                    tableDetailsCommand.CommandText = String.Format(
                        "SELECT DISTINCT " &
                            "c.column_name, c .data_type, c.is_nullable, tc.constraint_type, c.numeric_precision, c.numeric_scale, c.character_maximum_length, c.table_name, c.ordinal_position, tc.constraint_name " &
                        "FROM information_schema.columns c " &
                            "LEFT OUTER JOIN (" &
                                "information_schema.constraint_column_usage ccu " &
                                    "JOIN information_schema.table_constraints tc on (" &
                                        "tc.table_schema = ccu.table_schema " &
                                            "AND tc.constraint_name = ccu.constraint_name " &
                                            "AND tc.constraint_type <> 'CHECK'" &
                            ")" &
                        ") ON (" &
                            "c.table_schema = ccu.table_schema and ccu.table_schema = '{1}' " &
                            "AND c.table_name = ccu.table_name " &
                            "AND c.column_name = ccu.column_name" &
                        ")" &
                        "WHERE c.table_name = '{0}' " &
                            "AND c.table_schema ='{1}' " &
                        "ORDER BY c.table_name, c.ordinal_position", table.Name, owner)

                    Using sqlDataReader = tableDetailsCommand.ExecuteReader(CommandBehavior.[Default])
                        While sqlDataReader.Read()
                            Dim columnName As String = sqlDataReader.GetString(0)
                            Dim dataType As String = sqlDataReader.GetString(1)
                            Dim isNullable As Boolean = sqlDataReader.GetString(2).Equals("YES", StringComparison.CurrentCultureIgnoreCase)
                            'Dim characterMaxLenth As Integer? = CType(sqlDataReader("character_maximum_length"), Integer?)
                            Dim characterMaxLenth As Integer? = Nothing
                            If Not sqlDataReader.IsDBNull(sqlDataReader.GetOrdinal("character_maximum_length")) Then
                                characterMaxLenth = CInt(sqlDataReader("character_maximum_length"))
                            End If
                            'Dim numericPrecision As Integer? = CType(sqlDataReader("numeric_precision"), Integer?)
                            Dim numericPrecision As Integer? = Nothing
                            If Not sqlDataReader.IsDBNull(sqlDataReader.GetOrdinal("numeric_precision")) Then
                                numericPrecision = CInt(sqlDataReader("numeric_precision"))
                            End If
                            'Dim numericScale As Integer? = CType(sqlDataReader("numeric_scale"), Integer?)
                            Dim numericScale As Integer? = Nothing
                            If Not sqlDataReader.IsDBNull(sqlDataReader.GetOrdinal("numeric_scale")) Then
                                numericScale = CInt(sqlDataReader("numeric_scale"))
                            End If
                            Dim isPrimaryKey As Boolean = (Not sqlDataReader.IsDBNull(3) AndAlso sqlDataReader.GetString(3).Equals(SqlServerConstraintType.PrimaryKey.ToString(), StringComparison.CurrentCultureIgnoreCase))
                            Dim isForeignKey As Boolean = (Not sqlDataReader.IsDBNull(3) AndAlso sqlDataReader.GetString(3).Equals(SqlServerConstraintType.ForeignKey.ToString(), StringComparison.CurrentCultureIgnoreCase))

                            Dim m = New DataTypeMapper()

                            'IsPrimaryKey(selectedTableName.Name, columnName)
                            'IsFK()
                            columns.Add(New Column() With {
                                .Name = columnName,
                                .DataType = dataType,
                                .IsNullable = isNullable,
                                .IsPrimaryKey = isPrimaryKey,
                                .IsForeignKey = isForeignKey,
                                .MappedDataType = m.MapFromDBType(ServerType.SqlServer, dataType, characterMaxLenth, numericPrecision, numericScale).ToString(), _
                                .DataLength = characterMaxLenth,
                                .ConstraintName = sqlDataReader("constraint_name").ToString()
                             })

                            table.Columns = columns
                        End While
                        table.PrimaryKey = DeterminePrimaryKeys(table)
                        table.ForeignKeys = DetermineForeignKeyReferences(table)
                        table.HasManyRelationships = DetermineHasManyRelationships(table)
                    End Using
                End Using
                'Get references to current table
                Using ChildTablesCommand = conn.CreateCommand()
                    ChildTablesCommand.CommandText = SQL_GETREFTBL
                    ChildTablesCommand.Parameters.AddWithValue(SQL_GETREFTBL_PAR_TBL, table.Name)
                    Using ChildTablesReader As SqlDataReader = ChildTablesCommand.ExecuteReader
                        While ChildTablesReader.Read
                            table.ReferencingTables.Add(
                                New Reference(
                                    CStr(ChildTablesReader("ForeignKeyName")),
                                    CStr(ChildTablesReader("ForeignKeyTableName")),
                                    CStr(ChildTablesReader("ForeignKeyColumnName")),
                                    CStr(ChildTablesReader("ReferenceColumnName"))))
                        End While
                    End Using
                End Using

            End Using
        Finally
            conn.Close()
        End Try

        Return columns
    End Function

    Public Function GetOwners() As IList(Of String) Implements IMetadataReader.GetOwners
        Dim owners = New List(Of String)()
        Dim conn = New SqlConnection(connectionStr)
        conn.Open()
        Try
            Using conn
                Dim tableCommand = conn.CreateCommand()
                tableCommand.CommandText = "SELECT SCHEMA_NAME from INFORMATION_SCHEMA.SCHEMATA"
                Dim sqlDataReader = tableCommand.ExecuteReader(CommandBehavior.CloseConnection)
                While sqlDataReader.Read()
                    Dim ownerName = sqlDataReader.GetString(0)
                    owners.Add(ownerName)
                End While
            End Using
        Finally
            conn.Close()
        End Try
        Return owners
    End Function

    Public Function GetTables(owner As String) As List(Of Table) Implements IMetadataReader.GetTables
        Dim tables = New List(Of Table)()
        Dim conn = New SqlConnection(connectionStr)
        conn.Open()
        Try
            Using conn
                Dim tableCommand = conn.CreateCommand()
                tableCommand.CommandText = [String].Format(
                    "SELECT table_name from information_schema.tables " &
                    "WHERE table_type in ('BASE TABLE','VIEW') " &
                        "AND TABLE_SCHEMA = '{0}'", owner)
                Dim sqlDataReader = tableCommand.ExecuteReader(CommandBehavior.CloseConnection)
                While sqlDataReader.Read()
                    Dim tableName = sqlDataReader.GetString(0)
                    tables.Add(New Table() With {
                     .Name = tableName
                    })
                End While
            End Using
            tables.Sort(Function(x, y) [String].CompareOrdinal(x.Name, y.Name))
        Finally
            conn.Close()
        End Try
        Return tables
    End Function

    Public Function GetSequences(owner As String) As List(Of String) Implements IMetadataReader.GetSequences
        Return New List(Of String)()
    End Function

    Private Shared Function DeterminePrimaryKeys(table As Table) As PrimaryKey
        Dim primaryKeys As List(Of Column) = table.Columns.Where(Function(x) x.IsPrimaryKey.Equals(True)).ToList()
        If primaryKeys.Count() = 1 Then
            Dim c = primaryKeys.First()
            Dim key = New PrimaryKey() With {
                .Type = PrimaryKeyType.PrimaryKey,
                .Columns = {New Column() With {
                    .DataType = c.DataType,
                    .Name = c.Name}}.ToList}
            Return key
        Else
            Dim key = New PrimaryKey() With {
                .Type = PrimaryKeyType.CompositeKey
            }
            For Each primaryKey In primaryKeys
                key.Columns.Add(New Column() With {
                    .DataType = primaryKey.DataType,
                    .Name = primaryKey.Name
                })
            Next
            Return key
        End If
    End Function

    Private Function DetermineForeignKeyReferences(Tbl As Table) As List(Of ForeignKey)
        Dim constraints = Tbl.Columns.Where(
            Function(x) x.IsForeignKey.Equals(True)).Select(
                Function(x) x.ConstraintName).Distinct().ToList()
        Dim foreignKeys = New List(Of ForeignKey)()
        constraints.ForEach(
            Sub(c)
                Dim fkColumns = Tbl.Columns.Where(Function(x) x.ConstraintName.Equals(c)).ToArray()
                Dim fk = New ForeignKey() With {
                 .Name = fkColumns(0).Name,
                 .References = GetForeignKeyReferenceTableName(Tbl.Name, fkColumns(0).Name),
                 .AllColumnsNamesForTheSameConstraint = fkColumns.Select(Function(f) f.Name).ToArray()
                }
                foreignKeys.Add(CType(fk, ForeignKey))
            End Sub)
        Table.SetUniqueNamesForForeignKeyProperties(foreignKeys)
        Return foreignKeys
    End Function

    Private Function GetForeignKeyReferenceTableName(selectedTableName As String, columnName As String) As String
        Dim referencedTableName As Object

        Dim conn = New SqlConnection(connectionStr)
        conn.Open()
        Try
            Using conn
                Dim tableCommand As SqlCommand = conn.CreateCommand()
                tableCommand.CommandText = [String].Format(
                    "SELECT pk_table = pk.table_name " &
                    "FROM information_schema.referential_constraints c " &
                        "INNER JOIN information_schema.table_constraints fk ON c.constraint_name = fk.constraint_name " &
                        "INNER JOIN information_schema.table_constraints pk ON c.unique_constraint_name = pk.constraint_name " &
                        "INNER JOIN information_schema.key_column_usage cu ON c.constraint_name = cu.constraint_name " &
                        "INNER JOIN (" &
                            "SELECT i1.table_name, i2.column_name " &
                            "FROM information_schema.table_constraints i1 " &
                                "INNER JOIN information_schema.key_column_usage i2 ON i1.constraint_name = i2.constraint_name " &
                            "WHERE i1.constraint_type = 'PRIMARY KEY'" &
                        ") pt ON pt.table_name = pk.table_name " &
                    "WHERE fk.table_name = '{0}' AND cu.column_name = '{1}'", selectedTableName, columnName)
                referencedTableName = tableCommand.ExecuteScalar()
            End Using
        Finally
            conn.Close()
        End Try
        Return DirectCast(referencedTableName, String)
    End Function

    'http://blog.sqlauthority.com/2006/11/01/sql-server-query-to-display-foreign-key-relationships-and-name-of-the-constraint-for-each-table-in-database/
    Private Function DetermineHasManyRelationships(table As Table) As List(Of HasMany)
        Dim hasManyRelationships = New List(Of HasMany)()
        Dim conn = New SqlConnection(connectionStr)
        conn.Open()
        Try
            Using conn
                Using command = New SqlCommand()
                    command.Connection = conn
                    command.CommandText = [String].Format(
                        "SELECT DISTINCT " &
                            "PK_TABLE = b.TABLE_NAME, " &
                            "FK_TABLE = c.TABLE_NAME, " &
                            "FK_COLUMN_NAME = d.COLUMN_NAME, " &
                            "CONSTRAINT_NAME = a.CONSTRAINT_NAME " &
                        "FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS a " &
                            "JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS b ON a.CONSTRAINT_SCHEMA = b.CONSTRAINT_SCHEMA AND a.UNIQUE_CONSTRAINT_NAME = b.CONSTRAINT_NAME " &
                            "JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS c ON a.CONSTRAINT_SCHEMA = c.CONSTRAINT_SCHEMA AND a.CONSTRAINT_NAME = c.CONSTRAINT_NAME " &
                            "JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE d on a.CONSTRAINT_NAME = d.CONSTRAINT_NAME " &
                        "WHERE b.TABLE_NAME = '{0}' " &
                        "ORDER BY 1,2", table.Name)
                    Dim reader As SqlDataReader = command.ExecuteReader()

                    While reader.Read()
                        Dim constraintName = reader("CONSTRAINT_NAME").ToString()
                        Dim fkColumnName = reader("FK_COLUMN_NAME").ToString()
                        Dim existing = hasManyRelationships.FirstOrDefault(Function(hm) hm.ConstraintName = constraintName)
                        If existing Is Nothing Then
                            Dim newHasManyItem = New HasMany() With { _
                             .ConstraintName = constraintName, _
                             .Reference = reader.GetString(1) _
                            }
                            newHasManyItem.AllReferenceColumns.Add(fkColumnName)

                            hasManyRelationships.Add(newHasManyItem)
                        Else
                            existing.AllReferenceColumns.Add(fkColumnName)
                        End If
                    End While

                End Using
            End Using
        Finally
            conn.Close()
        End Try
        Return hasManyRelationships
    End Function


End Class

